/*
 Navicat Premium Dump SQL

 Source Server         : 127.0.0.1
 Source Server Type    : MySQL
 Source Server Version : 50744 (5.7.44)
 Source Host           : 127.0.0.1:3306
 Source Schema         : smart_community

 Target Server Type    : MySQL
 Target Server Version : 50744 (5.7.44)
 File Encoding         : 65001

 Date: 18/09/2025 14:10:31
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for sc_cleaning_area
-- ----------------------------
DROP TABLE IF EXISTS `sc_cleaning_area`;
CREATE TABLE `sc_cleaning_area` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '区域ID',
  `area_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '区域名称',
  `area_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '区域类型：building-楼栋，elevator-电梯，public-公共区域，green-绿化带',
  `location` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '具体位置',
  `cleaning_standard` text COLLATE utf8mb4_unicode_ci COMMENT '保洁标准',
  `frequency` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '保洁频次：daily-每日，weekly-每周，monthly-每月',
  `responsible_user_id` bigint(20) DEFAULT NULL COMMENT '负责人ID',
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态：1-启用，2-停用',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建人ID',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新人ID',
  `deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志：0-未删除，1-已删除',
  `version` int(11) NOT NULL DEFAULT '0' COMMENT '版本号',
  `community_id` bigint(20) NOT NULL COMMENT '所属社区ID',
  `remark` text COLLATE utf8mb4_unicode_ci COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_area_type` (`area_type`),
  KEY `idx_responsible_user_id` (`responsible_user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_create_time` (`create_time`),
  CONSTRAINT `fk_cleaning_area_user` FOREIGN KEY (`responsible_user_id`) REFERENCES `sc_user` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='保洁区域表';

-- ----------------------------
-- Records of sc_cleaning_area
-- ----------------------------
BEGIN;
INSERT INTO `sc_cleaning_area` (`id`, `area_name`, `area_type`, `location`, `cleaning_standard`, `frequency`, `responsible_user_id`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `deleted`, `version`, `community_id`, `remark`) VALUES (1, '111', '大厅', '111', '111', '每日', 46, 1, '2025-09-17 09:42:39', '2025-09-17 09:42:39', 42, 42, 0, 0, 23, NULL);
COMMIT;

-- ----------------------------
-- Table structure for sc_cleaning_area_user_rel
-- ----------------------------
DROP TABLE IF EXISTS `sc_cleaning_area_user_rel`;
CREATE TABLE `sc_cleaning_area_user_rel` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '关联ID',
  `area_id` bigint(20) NOT NULL COMMENT '区域ID',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `is_primary` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否主要负责人：0-否，1-是',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建人ID',
  `version` int(11) NOT NULL DEFAULT '0' COMMENT '版本号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_area_user` (`area_id`,`user_id`),
  KEY `idx_area_id` (`area_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_is_primary` (`is_primary`),
  KEY `idx_create_time` (`create_time`),
  CONSTRAINT `fk_cleaning_area_user_area` FOREIGN KEY (`area_id`) REFERENCES `sc_cleaning_area` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cleaning_area_user_user` FOREIGN KEY (`user_id`) REFERENCES `sc_user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='保洁区域负责人关联表';

-- ----------------------------
-- Records of sc_cleaning_area_user_rel
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for sc_cleaning_record
-- ----------------------------
DROP TABLE IF EXISTS `sc_cleaning_record`;
CREATE TABLE `sc_cleaning_record` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '记录ID',
  `task_id` bigint(20) NOT NULL COMMENT '任务ID',
  `user_id` bigint(20) NOT NULL COMMENT '执行用户ID',
  `completion_time` datetime NOT NULL COMMENT '完成时间',
  `before_photo` json DEFAULT NULL COMMENT '清洁前照片',
  `after_photo` json DEFAULT NULL COMMENT '清洁后照片',
  `tools_used` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '使用工具',
  `quality_score` decimal(3,1) DEFAULT NULL COMMENT '质量评分（1-10分）',
  `inspector_id` bigint(20) DEFAULT NULL COMMENT '检查人ID',
  `inspection_time` datetime DEFAULT NULL COMMENT '检查时间',
  `inspection_remark` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '检查备注',
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态：1-待检查，2-合格，3-不合格，4-需返工',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建人ID',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新人ID',
  `deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志：0-未删除，1-已删除',
  `version` int(11) NOT NULL DEFAULT '0' COMMENT '版本号',
  `remark` text COLLATE utf8mb4_unicode_ci COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_task_id` (`task_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_completion_time` (`completion_time`),
  KEY `idx_inspector_id` (`inspector_id`),
  KEY `idx_status` (`status`),
  KEY `idx_create_time` (`create_time`),
  CONSTRAINT `fk_cleaning_record_inspector` FOREIGN KEY (`inspector_id`) REFERENCES `sc_user` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_cleaning_record_task` FOREIGN KEY (`task_id`) REFERENCES `sc_cleaning_task` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cleaning_record_user` FOREIGN KEY (`user_id`) REFERENCES `sc_user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='保洁记录表';

-- ----------------------------
-- Records of sc_cleaning_record
-- ----------------------------
BEGIN;
INSERT INTO `sc_cleaning_record` (`id`, `task_id`, `user_id`, `completion_time`, `before_photo`, `after_photo`, `tools_used`, `quality_score`, `inspector_id`, `inspection_time`, `inspection_remark`, `status`, `create_time`, `update_time`, `create_by`, `update_by`, `deleted`, `version`, `remark`) VALUES (1, 1, 46, '2025-09-18 00:48:35', '[{\"id\": 12, \"status\": \"success\", \"fileUrl\": \"http://localhost:9000/smart-community/company_logo/20250819170517_349e2602f0e0473b937bb7776e5c46fa.jpg\", \"fileName\": \"162611_3.jpg\", \"filePath\": \"http://localhost:9000/smart-community/company_logo/20250819170517_349e2602f0e0473b937bb7776e5c46fa.jpg\", \"fileSize\": 51551, \"fileType\": \"image/jpeg\", \"createTime\": \"2025-09-10T06:39:01.913Z\", \"uploadType\": \"COMPANY_LOGO\", \"uploadSource\": \"WEB\", \"fileExtension\": \"jpg\", \"originalFileName\": \"162611_3.jpg\"}, {\"id\": 57, \"status\": \"success\", \"fileUrl\": \"http://localhost:9000/smart-community/other/20250909143910_ac855fb8ec0447109bb00174f49e44e8.png\", \"fileName\": \"微信图片_2025-09-05_175731_940.png\", \"filePath\": \"http://localhost:9000/smart-community/other/20250909143910_ac855fb8ec0447109bb00174f49e44e8.png\", \"fileSize\": 1190583, \"fileType\": \"image/jpeg\", \"createTime\": \"2025-09-10T06:39:01.913Z\", \"uploadType\": \"COMPANY_LOGO\", \"uploadSource\": \"WEB\", \"fileExtension\": \"jpg\", \"originalFileName\": \"微信图片_2025-09-05_175731_940.png\"}]', NULL, '拖把', NULL, NULL, NULL, NULL, 1, '2025-09-18 00:48:35', '2025-09-18 04:02:48', 46, 46, 0, 0, NULL);
COMMIT;

-- ----------------------------
-- Table structure for sc_cleaning_task
-- ----------------------------
DROP TABLE IF EXISTS `sc_cleaning_task`;
CREATE TABLE `sc_cleaning_task` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '任务ID',
  `area_id` bigint(20) NOT NULL COMMENT '区域ID',
  `task_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '任务名称',
  `task_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '任务类型：routine-常规，temporary-临时，special-特殊',
  `scheduled_date` date NOT NULL COMMENT '计划日期',
  `scheduled_time` time DEFAULT NULL COMMENT '计划时间',
  `assigned_user_id` bigint(20) DEFAULT NULL COMMENT '分配用户ID',
  `priority` tinyint(1) NOT NULL DEFAULT '1' COMMENT '优先级：1-低，2-中，3-高，4-紧急',
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态：1-待分配，2-已分配，3-进行中，4-已完成，5-已取消',
  `description` text COLLATE utf8mb4_unicode_ci COMMENT '任务描述',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建人ID',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新人ID',
  `deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志：0-未删除，1-已删除',
  `version` int(11) NOT NULL DEFAULT '0' COMMENT '版本号',
  `remark` text COLLATE utf8mb4_unicode_ci COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_area_id` (`area_id`),
  KEY `idx_assigned_user_id` (`assigned_user_id`),
  KEY `idx_scheduled_date` (`scheduled_date`),
  KEY `idx_status` (`status`),
  KEY `idx_priority` (`priority`),
  KEY `idx_create_time` (`create_time`),
  CONSTRAINT `fk_cleaning_task_area` FOREIGN KEY (`area_id`) REFERENCES `sc_cleaning_area` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cleaning_task_user` FOREIGN KEY (`assigned_user_id`) REFERENCES `sc_user` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='保洁任务表';

-- ----------------------------
-- Records of sc_cleaning_task
-- ----------------------------
BEGIN;
INSERT INTO `sc_cleaning_task` (`id`, `area_id`, `task_name`, `task_type`, `scheduled_date`, `scheduled_time`, `assigned_user_id`, `priority`, `status`, `description`, `create_time`, `update_time`, `create_by`, `update_by`, `deleted`, `version`, `remark`) VALUES (1, 1, 'task01', 'routine', '2025-01-30', '08:00:00', 46, 1, 3, 'task01', '2025-09-17 11:07:04', '2025-09-17 16:47:27', 42, 46, 0, 1, NULL);
INSERT INTO `sc_cleaning_task` (`id`, `area_id`, `task_name`, `task_type`, `scheduled_date`, `scheduled_time`, `assigned_user_id`, `priority`, `status`, `description`, `create_time`, `update_time`, `create_by`, `update_by`, `deleted`, `version`, `remark`) VALUES (2, 1, 'task02', 'special', '2025-02-08', '10:30:00', 44, 4, 2, 'task02', '2025-09-17 11:23:04', '2025-09-17 03:28:12', 42, 42, 0, 0, NULL);
INSERT INTO `sc_cleaning_task` (`id`, `area_id`, `task_name`, `task_type`, `scheduled_date`, `scheduled_time`, `assigned_user_id`, `priority`, `status`, `description`, `create_time`, `update_time`, `create_by`, `update_by`, `deleted`, `version`, `remark`) VALUES (3, 1, 'task03', 'temporary', '2025-02-08', '12:30:00', 42, 2, 2, 'task03', '2025-09-17 11:28:58', '2025-09-17 11:28:58', 42, 42, 0, 0, NULL);
INSERT INTO `sc_cleaning_task` (`id`, `area_id`, `task_name`, `task_type`, `scheduled_date`, `scheduled_time`, `assigned_user_id`, `priority`, `status`, `description`, `create_time`, `update_time`, `create_by`, `update_by`, `deleted`, `version`, `remark`) VALUES (4, 1, 'task04', 'temporary', '2025-02-08', '10:30:00', 42, 3, 2, 'task04', '2025-09-17 11:36:51', '2025-09-17 11:36:51', 42, 42, 0, 0, NULL);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
